<!doctype html>
<html>
<head>
	<title>Database model</title>
	<style type="text/css">
		.table {
			margin: 20px 10px;
			min-width: 500px;
			width: 500px;
			border-collapse: collapse;
			/*border-bottom: 2px solid black;
			border-top: 2px solid black;*/
			border-left: 5px solid blue;
			border-bottom: 1px solid black;
			border-top: 1px solid black;
			border-right: 1px solid black;
		}

		.table tr:nth-child(1) {
			border-bottom: 2px solid black;
		}

		.table td, .table th {
			padding: 5px 10px;
			text-align: left;
		}

		.table tr td:nth-child(1) {
			width: 150px;
		}

		.table tr td:nth-child(1) {
			font-family: Monospace;
		}

		:target tr:nth-child(1) {
			background-color: lightgreen;
		}
	</style>
</head>
<body>
	<h1>Database model</h1>

	<table id="dbinfo" class="table">
		<tr><th colspan="2">dbinfo</th></tr>
		<tr><td>version</td><td>integer not null</td></tr>
		<tr><td>cookiesecret</td><td>string not null</td></tr>
		<tr><td>sessionsecret</td><td>string not null</td></tr>
	</table>

	<table id="session" class="table">
		<tr><th colspan="2">session</th></tr>
		<tr><td>id</td><td>text identifier</td></tr>
		<tr><td>data</td><td>text not null</td></tr>
		<tr><td>created</td><td>timestamp not null</td></tr>
	</table>
	
	<table id="person" class="table">
		<tr><th colspan="2">person</th></tr>
		<tr><td>id</td><td>identifier</td></tr>
		<tr><td>name</td><td>string not null</td></tr>
		<tr><td>email</td><td>string unique not null</td></tr>
		<tr><td>password</td><td>string not null</td></tr>
		<tr><td>role</td><td>enum{customer, admin} not null</td></tr>
		<tr><td>created</td><td>timestamp not null</td></tr>
	</table>
	
	<table id="household" class="table">
		<tr><th colspan="2">household</th></tr>
		<tr><td>id</td><td>identifier</td></tr>
		<tr><td>name</td><td>text not null</td></tr>
		<tr><td>created</td><td>timestamp not null</td></tr>
	</table>
	
	<table id="household_member" class="table">
		<tr><th colspan="2">household_member</th></tr>
		<tr><td>household_id</td><td>foreign identifier to <a href="#household">household</a> not null</td></tr>
		<tr><td>person_id</td><td>foreign identifier to <a href="#person">person</a> not null</td></tr>
		<tr><td>role</td><td>enum{member, founder} not null</td></tr>
		<tr><td>created</td><td>timestamp not null</td></tr>
	</table>
	
	<table id="household_invitation" class="table">
		<tr><th colspan="2">household_invitation</th></tr>
		<tr><td>household_id</td><td>foreign identifier to <a href="#household">household</a> not null</td></tr>
		<tr><td>from_person_id</td><td>foreign identifier to <a href="#person">person</a> not null</td></tr>
		<tr><td>to_person_id</td><td>foreign identifier to <a href="#person">person</a> not null</td></tr>
		<tr><td>created</td><td>timestamp not null</td></tr>
	</table>
	
	<table id="shopping_list" class="table">
		<tr><th colspan="2">shopping_list</th></tr>
		<tr><td>id</td><td>identifier</td></tr>
		<tr><td>shop_name</td><td>string not null</td></tr>
		<tr><td>household_id</td><td>foreign identifier to <a href="#household">household</a> not null</td></tr>
		<tr><td>buyer_person_id</td><td>foreign identifier to <a href="#person">person</a> not null</td></tr>
		<tr><td>creator_person_id</td><td>foreign identifier to <a href="#person">person</a> not null</td></tr>
		<tr><td>shopped</td><td>timestamp not null</td></tr>
		<tr><td>created</td><td>timestamp not null</td></tr>
		<tr><td>(shop_id)</td><td>foreign identifier to <a href="#shop">shop</a></td></tr>
	</table>
	
	<table id="shopping_item" class="table">
		<tr><th colspan="2">shopping_item</th></tr>
		<tr><td>id</td><td>identifier</td></tr>
		<tr><td>name</td><td>string not null</td></tr>
		<tr><td>shopping_list_id</td><td>foreign identifier to <a href="#shopping_list">shopping_list</a> not null</td></tr>
		<tr><td>owner_person_id</td><td>foreign identifier to <a href="#person">person</a> not null</td></tr>
		<tr><td>price</td><td>money not null</td></tr>
		<tr><td>barcode</td><td>string</td></tr>
	</table>
	
	<table id="shop" class="table">
		<tr><th colspan="2">(shop)</th></tr>
		<tr><td>id</td><td>identifier</td></tr>
		<tr><td>name</td><td>string not null</td></tr>
		<tr><td>location</td><td>geo coordinates</td></tr>
	</table>
	
	<table id="notification" class="table">
		<tr><th colspan="2">notification</th></tr>
		<tr><td>id</td><td>identifier</td></tr>
		<tr><td>created</td><td>timestamp not null</td></tr>
		<tr><td>read</td><td>boolean not null default=false</td></tr>
		<tr><td>recipient_person_id</td><td>foreign identifier to <a href="#person">person</a></td></tr>
		<tr><td>type</td><td>text not null (could be 'new_household_member' ...)</td></tr>
		<tr><td>data</td><td>text not null (could be id of new member)</td></tr>
	</table>
</body>
</html>